package com.ch.mafia.base;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;

import org.json.JSONObject;

import com.ch.mafia.db.MyConnection;
import com.mysql.jdbc.Statement;

public class BaseDao {
	protected Connection con;
	protected HashSet<String> colsSet;
	public Integer add(JSONObject jsonObject) throws Exception{
		int result = 0;
		ResultSet rs =null;
		PreparedStatement ps = null;
		try{
			con = MyConnection.GetConnection();
			StringBuilder sql = new StringBuilder();
			List<String> cols = new ArrayList<String>();
			
			@SuppressWarnings("unchecked")
			Iterator<String> it =(Iterator<String>)jsonObject.keys();
			while(it.hasNext()){
				String key = it.next();
				if(!key.equals("tableName"))
					cols.add(key);
			}
			sql.append("insert into ");
			sql.append(jsonObject.get("tableName").toString().toLowerCase());
			sql.append(" (");
			
			for(int i=0;i<cols.size();i++){
				if(colsSet==null){
					sql.append(cols.get(i));
					sql.append(",");
					
				}else if(colsSet.contains(cols.get(i))){
					sql.append(cols.get(i));
					sql.append(",");
				}
					
			}
			sql.setLength(sql.length()-1);
			sql.append(") values(");
			for(int i=0;i<cols.size();i++){
				if(colsSet==null){
					sql.append("'");
					sql.append(jsonObject.get(cols.get(i)));
					sql.append("'");
					sql.append(",");
				}else if(colsSet.contains(cols.get(i))){
					sql.append("'");
					sql.append(jsonObject.get(cols.get(i)));
					sql.append("'");
					if(i!=cols.size()-1){
						sql.append(",");
					}
				}
				
			}
			sql.setLength(sql.length()-1);
			sql.append(")");
			System.out.println("sql:"+sql);
			ps = con.prepareStatement(sql.toString(),Statement.RETURN_GENERATED_KEYS);
			ps.executeUpdate();
			rs = ps.getGeneratedKeys();
			if(rs.next()){
				result = rs.getInt(1);
			}
		}
		finally{
			if(ps!=null){
				ps.close();
			}
			if(con!=null){
				con.close();
			}
		}
		return result;
	}
	
	public int update(String tableName,String[] updateCols,String[] values,String where,String[] whereValues) throws Exception{
		int result = 0;
		ResultSet rs =null;
		PreparedStatement ps = null;
		try{
			con = MyConnection.GetConnection();
			StringBuilder sql = new StringBuilder();
			sql.append("update ");
			sql.append(tableName.toLowerCase());
			sql.append(" ");
			sql.append(" set ");
			for(int i=0;i<updateCols.length;i++){
				sql.append(updateCols[i]);
				sql.append("=");
				sql.append(values[i]);
				sql.append(",");
			}
			sql.setLength(sql.length()-1);
			if(where!=null && !where.equals("")){
				sql.append(" where ");
				sql.append(where);
			}
			
			
			ps =con.prepareStatement(sql.toString());
			if(whereValues!=null && whereValues.length>0){
				for(int i=0;i<whereValues.length;i++){
					ps.setString(i+1,whereValues[i]);
				}
			}
			System.out.println("sql:"+sql);
			ps = con.prepareStatement(sql.toString(),Statement.RETURN_GENERATED_KEYS);
			ps.executeUpdate();
			rs = ps.getGeneratedKeys();
			if(rs.next()){
				result = rs.getInt(1);
			}
		}
		finally{
			if(ps!=null){
				ps.close();
			}
			if(con!=null){
				con.close();
			}
		}
		return result;
	}
	
	public JSONObject queryBean(String tableName,String[] resultCols,String where,String[] whereValues) throws Exception{
		JSONObject result =null;
		PreparedStatement ps = null;
		ResultSet rs =null;
		try{
			con = MyConnection.GetConnection();
			StringBuilder sql = new StringBuilder();
			if(resultCols!=null){
				sql.append("select ");
				for(int i=0;i<resultCols.length;i++){
					sql.append(resultCols[i]);
					if(i!=resultCols.length-1){
						sql.append(",");
					}
				}
			}else{
				sql.append("select * ");
			}
			sql.append(" from ");
			sql.append(tableName.toLowerCase());
			if(where!=null && !where.equals("")){
				sql.append(" where ");
				sql.append(where);
			}
			System.out.println("sql:"+sql);
			ps =con.prepareStatement(sql.toString());
			if(whereValues!=null && whereValues.length>0){
				for(int i=0;i<whereValues.length;i++){
					ps.setString(i+1,whereValues[i]);
				}
			}
			rs = ps.executeQuery();
			
			while(rs!=null && rs.next()){
				if(result==null){
					result = new JSONObject();
				}
				for(int i=0;i<resultCols.length;i++){
					String value = rs.getString(resultCols[i]);
					result.put(resultCols[i], value);
				}
				
			}
		}
		finally{
			if(ps!=null){
				ps.close();
			}
			if(con!=null){
				con.close();
			}
		}
		
		return result;
	}
	
	public void deleteById(String tableName,String id) throws Exception{
		PreparedStatement ps = null;
		try{
			con = MyConnection.GetConnection();
			StringBuilder sql = new StringBuilder();
			sql.append("delete from ");
			sql.append(tableName.toLowerCase());
			sql.append(" where id = ");
			sql.append(id);
			ps =con.prepareStatement(sql.toString());
			ps.execute();
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(ps!=null){
				ps.close();
			}
			if(con!=null){
				con.close();
			}
		}
	}
	
	public void delete(String tableName,String where) throws Exception{
		PreparedStatement ps = null;
		try{
			con = MyConnection.GetConnection();
			StringBuilder sql = new StringBuilder();
			sql.append("delete from ");
			sql.append(tableName.toLowerCase());
			if(where!=null){
				sql.append(" where ");
			}
			ps =con.prepareStatement(sql.toString());
			ps.execute();
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(ps!=null){
				ps.close();
			}
			if(con!=null){
				con.close();
			}
		}
	}
	
	
	public List<JSONObject> queryList(String tableName,String[] resultCols,String where,String[] whereValues,String orderBy,String limit) throws Exception{
		List<JSONObject> result =null;
		PreparedStatement ps = null;
		ResultSet rs =null;
		try{
			con = MyConnection.GetConnection();
			StringBuilder sql = new StringBuilder();
			sql.append("select * from ");
			sql.append(tableName.toLowerCase());
			if(where!=null && !where.equals("")){
				sql.append(" where ");
				sql.append(where);
			}
			
			if(orderBy!=null && !orderBy.equals("")){
				sql.append(" order by ");
				sql.append(orderBy);
			}
			if(limit!=null && !limit.equals("")){
				sql.append(" limit ");
				sql.append(limit);
			}
			System.out.println("sql:"+sql);
			ps =con.prepareStatement(sql.toString());
			if(whereValues!=null && whereValues.length>0){
				for(int i=0;i<whereValues.length;i++){
					ps.setString(i+1,whereValues[i]);
				}
			}
			rs = ps.executeQuery();
			while(rs!=null && rs.next()){
				if(result==null){
					result = new ArrayList<JSONObject>();
				}
				JSONObject jsonObject = new JSONObject();
				for(int i=0;i<resultCols.length;i++){
					String value = rs.getString(resultCols[i]);
					jsonObject.put(resultCols[i], value);
				}
				result.add(jsonObject);
			}
		}
		finally{
			if(ps!=null){
				ps.close();
			}
			if(con!=null){
				con.close();
			}
		}
		
		return result;
	}
}
